package com.tjjingpan.databasetest

import android.content.ContentValues
import androidx.appcompat.app.AppCompatActivity
import android.os.Bundle
import android.util.Log
import androidx.core.content.contentValuesOf
import kotlinx.android.synthetic.main.activity_main.*
import java.lang.Exception
import java.lang.NullPointerException

class MainActivity : AppCompatActivity() {
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
        val dbHelper = MyDatabaseHelper(this,"BookStore.db",1)
        createDatabase.setOnClickListener {
            dbHelper.writableDatabase
        }

        addData.setOnClickListener {
            val db = dbHelper.writableDatabase
            val values1 = ContentValues().apply {
                //开始组装第一条数据
                put("name","The Da Vinci Code")
                put("author","Dan Brown")
                put("pages",454)
                put("price",16.96)
            }
            db.insert("Book",null,values1) //插入第一条数据
            val values2 = ContentValues().apply {
                //开始组装第二条数据
                put("name","The Lost Symbol")
                put("author","Dan Brown")
                put("pages",510)
                put("price",19.95)
            }
           // db.insert("Book",null,values2)  //播入第二条数据

            //使用自定义的cvOf简化ContentValues的用法
            val values3 = cvOf("name" to "Games of Thrones","author" to "George Martin",
                "pages" to 720,"price" to "20.08")
           // db.insert("Book",null,values3)

            val values4 = contentValuesOf("name" to "Games of Thrones","author" to "George Martin",
                "pages" to 720,"price" to "20.08")
           // db.insert("Book",null,values4)

            //使用SQL操作数据库
           // db.execSQL("insert into Book(name,author,pages,price) values(?,?,?,?)",
            //        arrayOf("The Da Vinci Code","Dan Brown",454,"16.96"))
        }

        updateData.setOnClickListener {
            val db = dbHelper.writableDatabase
            val values = ContentValues()
            values.put("price",10.99)
            db.update("Book",values,"name = ?", arrayOf("The Da Vinci Code"))
            //使用SQL操作数据库
            db.execSQL("update Book set price = ? where name = ?",arrayOf("10.99","The Da Vinci Code"))
        }
        deleteData.setOnClickListener {
            val db = dbHelper.writableDatabase
            db.delete("Book","pages > ?", arrayOf("500"))

            db.execSQL("delete from Book where pages > ?", arrayOf("500"))
            db.delete("Book",null,null)
        }

        queryData.setOnClickListener {
            val db = dbHelper.writableDatabase
            //查询Book表中所有的数据
            val cursor = db.query("Book",null,null,null,null,null,null)

            //使用SQL操作数据库
            val cursor1 = db.rawQuery("select * from Book",null)
            if(cursor.moveToFirst()){
                do{
                    //遍历Cursor对象，取出数据并打印
                    val name = cursor.getString(cursor.getColumnIndex("name"))
                    val author = cursor.getString(cursor.getColumnIndex("author"))
                    val pages = cursor.getString(cursor.getColumnIndex("pages"))
                    val price = cursor.getString(cursor.getColumnIndex("price"))
                    Log.d("MainActivity","book name is $name")
                    Log.d("MainActivity","book author is $author")
                    Log.d("MainActivity","book pages is $pages")
                    Log.d("MainActivity","book price is $price")
                }while (cursor.moveToNext())
            }
            cursor.close()

        }

        replaceData.setOnClickListener {
            val db = dbHelper.writableDatabase
            db.beginTransaction()
            try {
                db.delete("Book",null,null)
                if(true)
                {
                    //手动抛出一个异常，让事务失败
                   // throw NullPointerException()
                }
                val values = ContentValues().apply {
                    put("name","Game of Thrones")
                    put("author","George Martin")
                    put("pages",720)
                    put("price","20.85")
                }
                db.insert("Book",null,values)
                db.setTransactionSuccessful() //事务已经执行成功
            }catch(e:Exception){
                e.printStackTrace()
            }finally {
                db.endTransaction()//结束事务
            }
        }
    }
}